﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace YanZhiwei.DotNet2.Utilities.Common
{
    /// <summary>
    /// 数据库操作的帮助类
    /// </summary>
    public static class DBHelper
    {
        #region DataTable的group by sum计算
        /// <summary>
        /// DataTable的group by sum计算
        ///<para>eg:DBHelper.GroupByToSum(_dt, "CTLampType", "钠灯- 100W", "CTLastMonthCount");</para> 
        /// </summary>
        /// <param name="datatable">DataTable</param>
        /// <param name="groupColumn">group by的列</param>
        /// <param name="sumColumn">sum的列</param>
        /// <returns>计算的值</returns>
        public static object GroupByToSum(this DataTable datatable, string groupColumn, string groupValue, string sumColumn)
        {
            object _result = null;
            if (datatable != null && !string.IsNullOrEmpty(groupColumn) && !string.IsNullOrEmpty(sumColumn))
            {
                _result = datatable.Compute("Sum(" + sumColumn + ")", "" + groupColumn + "='" + groupValue + "'");
            }
            return _result;
        }
        #endregion
        #region DataTable的列求和
        /// <summary>
        /// DataTable的列求和
        /// </summary>
        /// <param name="datatable">DataTable</param>
        /// <param name="sumColumn">sum的列</param>
        /// <returns>计算的值</returns>
        public static object Sum(this DataTable datatable, string sumColumn)
        {
            object _result = null;
            if (datatable != null && !string.IsNullOrEmpty(sumColumn))
            {
                _result = datatable.Compute("Sum(" + sumColumn + ")", string.Empty);
            }
            return _result;
        }
        #endregion
        #region 将LIST集合转换对应的DataTable
        /// <summary>
        /// 将LIST集合转换对应的DataTable
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="data">集合</param>
        /// <returns>将LIST集合转换对应的DataTable</returns>
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection _props = TypeDescriptor.GetProperties(typeof(T));
            DataTable _table = new DataTable();
            _table = CreateDbColumns(_props, _table);
            _table = FillDbToRows(data, _props, _table);
            return _table;
        }
        private static DataTable FillDbToRows<T>(IList<T> data, PropertyDescriptorCollection _props, DataTable _table)
        {
            object[] _values = new object[_props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < _values.Length; i++)
                {
                    _values[i] = _props[i].GetValue(item);
                }
                _table.Rows.Add(_values);
            }
            return _table;
        }
        private static DataTable CreateDbColumns(PropertyDescriptorCollection _props, DataTable _table)
        {
            if (_table != null && _props != null)
            {
                for (int i = 0; i < _props.Count; i++)
                {
                    PropertyDescriptor prop = _props[i];
                    _table.Columns.Add(prop.Name, prop.PropertyType);
                }
            }
            return _table;
        }
        #endregion
        #region 创建Datatable，规范：列名|列类型,列名|列类型,列名|列类型
        /// <summary>
        /// 创建Datatable，规范：列名|列类型,列名|列类型,列名|列类型
        ///<para>举例：CustomeName|string,Gender|bool,Address</para> 
        /// </summary>
        /// <param name="columnsInfo"></param>
        /// <returns></returns>
        public static DataTable CreateTable(string columnsInfo)
        {
            DataTable _dtNew = new DataTable();
            string[] _columnsList = columnsInfo.Split(',');
            string _columnName;
            string _columnType;
            string[] _singleColumnInfo;
            foreach (string s in _columnsList)
            {
                _singleColumnInfo = s.Split('|');
                _columnName = _singleColumnInfo[0];
                if (_singleColumnInfo.Length == 2)
                {
                    _columnType = _singleColumnInfo[1];
                    _dtNew.Columns.Add(new DataColumn(_columnName, Type.GetType(HandleColType(_columnType))));
                }
                else
                {
                    _dtNew.Columns.Add(new DataColumn(_columnName));
                }
            }
            return _dtNew;
        }
        private static string HandleColType(string columnType)
        {
            string _currentType = string.Empty;
            switch (columnType.ToLower())
            {
                case "int":
                    _currentType = "System.Int32";
                    break;
                case "string":
                    _currentType = "System.String";
                    break;
                case "decimal":
                    _currentType = "System.Decimal";
                    break;
                case "double":
                    _currentType = "System.Double";
                    break;
                case "dateTime":
                    _currentType = "System.DateTime";
                    break;
                case "bool":
                    _currentType = "System.Boolean";
                    break;
                case "image":
                    _currentType = "System.Byte[]";
                    break;
                case "object":
                    _currentType = "System.Object";
                    break;
                default:
                    _currentType = "System.String";
                    break;
            }
            return _currentType;
        }
        #endregion
        #region 是否是Smallint类型
        /// <summary>
        /// 是否是Smallint类型
        /// </summary>
        /// <param name="value">判断字符串</param>
        /// <param name="number">short</param>
        /// <returns>是否是Smallint类型</returns>
        public static bool CheckSmallint(string value, out short number)
        {
            number = -1;
            return short.TryParse(value, out number);
        }
        #endregion
        #region 是否是Bigint类型
        /// <summary>
        /// 是否是Bigint类型
        /// </summary>
        /// <param name="value">判断字符串</param>
        /// <param name="number">Bigint</param>
        /// <returns>是否是Bigint类型</returns>
        public static bool CheckBigint(string value, out long number)
        {
            number = -1;
            return long.TryParse(value, out number);
        }
        #endregion
        #region 是否是Tinyint
        /// <summary>
        /// 是否是Tinyint
        /// </summary>
        /// <param name="value">判断字符串</param>
        /// <param name="number">Tinyint</param>
        /// <returns>是否是Tinyint类型</returns>
        public static bool CheckTinyint(string value, out byte number)
        {
            number = 0;
            return byte.TryParse(value, out number);
        }
        #endregion
        #region 创建MS SQL链接字符串
        /// <summary>
        /// 创建MS SQL链接字符串
        /// </summary>
        /// <param name="server">Server</param>
        /// <param name="database">DataBase</param>
        /// <param name="uid">uid</param>
        /// <param name="pwd">pwd</param>
        /// <returns>MS SQL链接字符串</returns>
        public static string BuilderMSSqlConnectString(string server, string database, string uid, string pwd)
        {
            return string.Format(@"Server={0};DataBase={1};uid={2};pwd={3}", server, database, uid, pwd);
        }
        #endregion
        #region SQL注入筛选
        /// <summary>
        /// SQL注入筛选
        /// </summary>
        /// <param name="str">sql语句</param>
        /// <returns>SQL注入筛选</returns>
        public static string FilterSqlInjection(string str)
        {
            if (!string.IsNullOrEmpty(str))
            {
                str = str.Replace(";", "");
                str = str.Replace("'", "");
                str = str.Replace("&", "");
                str = str.Replace("%20", "");
                str = str.Replace("-", "");
                str = str.Replace("=", "");
                str = str.Replace("==", "");
                str = str.Replace("<", "");
                str = str.Replace(">", "");
                str = str.Replace("%", "");
                str = str.Replace(" or", "");
                str = str.Replace("or ", "");
                str = str.Replace(" and", "");
                str = str.Replace("and ", "");
                str = str.Replace(" not", "");
                str = str.Replace("not ", "");
                str = str.Replace("!", "");
                str = str.Replace("{", "");
                str = str.Replace("}", "");
                str = str.Replace("[", "");
                str = str.Replace("]", "");
                str = str.Replace("(", "");
                str = str.Replace(")", "");
                str = str.Replace("|", "");
                str = str.Replace("_", "");
            }
            return str;
        }
        #endregion
        #region 利用[ROW_NUMBER() over]分页，生成sql语句
        /// <summary>
        /// 利用[ROW_NUMBER() over]分页，生成sql语句
        /// </summary>
        /// <param name="tableName">表名称『eg:Orders』</param>
        /// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
        /// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>
        /// <param name="pSize">每页页数『需大于零』</param>
        /// <param name="pIndex">页数『从壹开始算』</param>
        /// <returns>生成分页sql脚本</returns>
        public static string PageDBWithRowNumberString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)
        {
            int _pageStart = pSize * (pIndex - 1) + 1;
            int _pageEnd = pSize * pIndex + 1;

            string _sql = string.Format("select * from  (select (ROW_NUMBER() over(order by {2} {3})) as ROWNUMBER,{1}  from {0})as tp where ROWNUMBER >= {4} and ROWNUMBER< {5} ",
                                         tableName,
                                         columns,
                                         orderColumn,
                                         orderType == 1 ? "desc" : "asc",
                                         _pageStart,
                                         _pageEnd);
            return _sql;
        }
        #endregion
        #region 利用[Top NotIn]分页，生成sql语句
        /// <summary>
        /// 利用[Top NotIn]分页，生成sql语句
        /// </summary>
        /// <param name="tableName">表名称『eg:Orders』</param>
        /// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
        /// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>
        /// <param name="pSize">每页页数『需大于零』</param>
        /// <param name="pIndex">页数『从壹开始算』</param>
        /// <returns>生成分页sql脚本</returns>
        public static string PageDBWithTopNotInString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)
        {
            /*
             *eg:
             *1=>SELECT orderID FROM Orders ORDER BY orderID
             *2=>SELECT TOP 20 orderID FROM Orders ORDER BY orderID //查询前一页数据 
             *3=> SELECT TOP 10 * FROM Orders WHERE (orderID NOT IN (SELECT TOP 20 orderID FROM Orders ORDER BY orderID)) ORDER BY orderID //在所有数据中，截去掉上一页数据(not in)，然后select top 10 即当前页数据
             */
            string _sql = string.Format("SELECT TOP {4} {1} FROM {0} WHERE ({2} NOT IN (SELECT TOP {5} {2} FROM {0} ORDER BY {2} {3})) ORDER BY {2} {3}",
                                         tableName,
                                         columns,
                                         orderColumn,
                                         orderType == 1 ? "desc" : "asc",
                                         pSize,
                                         (pIndex - 1) * pSize);
            return _sql;
        }
        #endregion
        #region 利用[Top Max]分页，生成sql语句
        /// <summary>
        /// 利用[Top Max]分页，生成sql语句
        /// </summary>
        /// <param name="tableName">表名称『eg:Orders』</param>
        /// <param name="columns">需要显示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>
        /// <param name="orderColumn">依据排序的列『eg:OrderID』</param>
        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>
        /// <param name="pSize">每页页数『需大于零』</param>
        /// <param name="pIndex">页数『从壹开始算』</param>
        /// <returns>生成分页sql脚本</returns>
        public static string PageDBWithTopMaxString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)
        {
            /*
             *eg:
             *1=>select top 30 orderID from Orders order by orderID asc
             *2=>(select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T) //查询前一页数据 
             *3=> select top 15 OrderID,OrderDate,ShipName,ShipCountry from Orders where orderID> 
                  ISNULL((select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T),0) 
                  order by orderID asc
             */
            string _sql = string.Format("select top {4} {1} from {0} where {2}> ISNULL((select max ({2}) from (select top {5} {2} from {0} order by {2} {3}) as T),0) order by {2} {3}",
                                         tableName,
                                         columns,
                                         orderColumn,
                                         orderType == 1 ? "desc" : "asc",
                                         pSize,
                                         (pIndex - 1) * pSize);
            return _sql;
        }
        #endregion
        #region 获取分页总页数
        /// <summary>
        /// 获取分页总页数
        /// </summary>
        /// <param name="recordCnt">总记录条数</param>
        /// <param name="pageSize">每页页数</param>
        /// <returns>分页总页数</returns>
        public static int GetPageTotalCount(int recordCnt, int pageSize)
        {
            int _pageTotalCount = recordCnt / pageSize;
            if (recordCnt % pageSize != 0)
                _pageTotalCount++;
            return _pageTotalCount;
        }
        #endregion
        #region DataTable中列值为空检测，若等于空，则返回failValue
        /// <summary>
        /// DataTable中列值为空检测，若等于空，则返回failValue
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <param name="columnName">列名称</param>
        /// <param name="failValue">若列等于NULL，需要返回的值</param>
        /// <returns>若列不等于NULL则返回实际值</returns>
        public static string ToString(this DataRow row, string columnName, string failValue)
        {
            if (row != null)
            {
                failValue = row.IsNull(columnName) == true ? failValue : row[columnName].ToString();
            }
            return failValue;
        }
        /// <summary>
        /// DataTable中列值为空检测，若等于空，则返回failValue
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <param name="columnIndex">列索引</param>
        /// <param name="failValue">若列等于NULL，需要返回的值</param>
        /// <returns>若列不等于NULL则返回实际值<</returns>
        public static string ToString(this DataRow row, int columnIndex, string failValue)
        {
            if (row != null)
            {
                int _columnCount = row.ItemArray.Length;
                if (columnIndex > _columnCount)
                    throw new ArgumentException("columnIndex");
                failValue = row.IsNull(columnIndex) == true ? failValue : row[columnIndex].ToString().Trim();
            }
            return failValue;
        }

        /// <summary>
        /// DataTable中列值为空检测，若等于空，则返回failValue
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <param name="columnIndex">列名称</param>
        /// <param name="failValue">若列等于NULL，需要返回的值</param>
        /// <returns>若列不等于NULL则返回实际值<</returns>
        public static int ToInt(this DataRow row, string columnName, int failValue)
        {
            if (row != null)
            {
                if (!row.Table.Columns.Contains(columnName))
                    throw new ArgumentException("columnName");
                if (row.IsNull(columnName))
                {
                    int.TryParse(row[columnName].ToString(), out failValue);
                }
            }
            return failValue;
        }
        /// <summary>
        /// DataTable中列值为空检测，若等于空，则返回failValue
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <param name="columnIndex">列索引</param>
        /// <param name="failValue">若列等于NULL，需要返回的值</param>
        /// <returns>若列不等于NULL则返回实际值<</returns>
        public static int ToInt(this DataRow row, int columnIndex, int failValue)
        {
            if (row != null)
            {
                int _columnCount = row.ItemArray.Length;
                if (columnIndex > _columnCount)
                    throw new ArgumentException("columnIndex");
                if (row.IsNull(columnIndex))
                {
                    int.TryParse(row[columnIndex].ToString(), out failValue);
                }
            }
            return failValue;
        }
        #endregion
        #region 将DataTable导出到CSV.
        /// <summary>
        /// 将DataTable导出到CSV.
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="fullSavePath">保存路径</param>
        /// <param name="tableheader">标题信息</param>
        /// <param name="columname">列名称『eg:姓名,年龄』</param>
        /// <returns>导出成功true;导出失败false</returns>
        public static bool ToCSV(this DataTable table, string fullSavePath, string tableheader, string columname)
        {
            //------------------------------------------------------------------------------------
            try
            {
                string _bufferLine = "";
                using (StreamWriter _writerObj = new StreamWriter(fullSavePath, false, Encoding.UTF8))
                {
                    if (!string.IsNullOrEmpty(tableheader))
                        _writerObj.WriteLine(tableheader);
                    if (!string.IsNullOrEmpty(columname))
                        _writerObj.WriteLine(columname);
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        _bufferLine = "";
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (j > 0)
                                _bufferLine += ",";
                            _bufferLine += table.Rows[i][j].ToString();
                        }
                        _writerObj.WriteLine(_bufferLine);
                    }
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        /// 将CSV文件数据导入到Datable中
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filePath">DataTable</param>
        /// <param name="rowIndex">保存路径</param>
        /// <returns>Datable</returns>
        public static DataTable AppendCSVRecord(this DataTable table, string filePath, int rowIndex)
        {
            using (StreamReader reader = new StreamReader(filePath, Encoding.UTF8, false))
            {
                int i = 0, j = 0;
                reader.Peek();
                while (reader.Peek() > 0)
                {
                    j = j + 1;
                    string _line = reader.ReadLine();
                    if (j >= rowIndex + 1)
                    {
                        string[] _split = _line.Split(',');
                        DataRow _row = table.NewRow();
                        for (i = 0; i < _split.Length; i++)
                        {
                            _row[i] = _split[i];
                        }
                        table.Rows.Add(_row);
                    }
                }
                return table;
            }
        }
        #endregion
        #region 过滤SQL不安全的字符串
        /// <summary>
        /// 过滤SQL不安全的字符串
        /// </summary>
        /// <param name="data">字符串</param>
        /// <returns>过滤后的字符串</returns>
        public static string FilterUnSafeSQL(this string data)
        {
            if (!string.IsNullOrEmpty(data))
            {
                data = data.Replace("'", "");
                data = data.Replace("\"", "");
                data = data.Replace("&", "&amp");
                data = data.Replace("<", "&lt");
                data = data.Replace(">", "&gt");
                data = Regex.Replace(data, "select", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "insert", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "delete from", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "count''", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "drop table", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "truncate", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "asc", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "mid", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "char", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "xp_cmdshell", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "exec master", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "net localgroup administrators", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "and", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "net user", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "or", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "net", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "-", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "delete", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "drop", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "script", "", RegexOptions.IgnoreCase);
            }
            return data;
        }
        #endregion
        #region 过滤SQL语句字符串中的注入脚本
        /// <summary>
        /// 过滤SQL语句字符串中的注入脚本
        /// </summary>
        /// <param name="data">传入的字符串</param>
        /// <returns>过滤后的字符串</returns>
        public static string FilterSqlInject(string data)
        {
            if (!string.IsNullOrEmpty(data))
            {
                data = data.Replace("'", "''");
                data = data.Replace(";", "；");
                data = data.Replace("(", "（");
                data = data.Replace(")", "）");
                data = data.Replace("Exec", "");
                data = data.Replace("Execute", "");
                data = data.Replace("xp_", "x p_");
                data = data.Replace("sp_", "s p_");
                data = data.Replace("0x", "0 x");
            }
            return data;
        }
        #endregion
        #region 过滤特殊字符
        /// <summary>
        /// 过滤特殊字符
        /// </summary>
        /// <param name="data">字符串</param>
        /// <returns>过滤后的字符串</returns>
        public static string FilterSpecial(string data)
        {
            if (!string.IsNullOrEmpty(data))
            {
                data = data.Replace("<", "");
                data = data.Replace(">", "");
                data = data.Replace("*", "");
                data = data.Replace("-", "");
                data = data.Replace("?", "");
                data = data.Replace("'", "''");
                data = data.Replace(",", "");
                data = data.Replace("/", "");
                data = data.Replace(";", "");
                data = data.Replace("*/", "");
                data = data.Replace("\r\n", "");
            }
            return data;
        }
        #endregion
        #region 过滤HTML标记
        /// <summary>
        /// 过滤HTML标记
        /// </summary>
        /// <param name="data">包括HTML，脚本，数据库关键字，特殊字符的源码 </param>
        /// <returns>已经去除标记后的文字</returns>
        public static string FilterHtmlTag(this string data)
        {
            if (!string.IsNullOrEmpty(data))
            {
                data = Regex.Replace(data, @"<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"<(.[^>]*)>", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"([\r\n])[\s]+", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"-->", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"<!--.*", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(quot|#34);", "\"", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(amp|#38);", "&", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(lt|#60);", "<", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(gt|#62);", ">", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(nbsp|#160);", " ", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(iexcl|#161);", "\xa1", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(cent|#162);", "\xa2", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(pound|#163);", "\xa3", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&(copy|#169);", "\xa9", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, @"&#(\d+);", "", RegexOptions.IgnoreCase);
                data = Regex.Replace(data, "xp_cmdshell", "", RegexOptions.IgnoreCase);
            }
            return data;
        }
        #endregion
        #region 过滤字符串【HTML标记，敏感SQL操作关键，特殊字符】
        /// <summary>
        /// 过滤字符串【HTML标记，敏感SQL操作关键，特殊字符】
        /// </summary>
        /// <param name="data">字符串</param>
        /// <returns>处理后的字符串</returns>
        public static string FilterString(this string data)
        {
            if (!string.IsNullOrEmpty(data))
            {
                data = FilterHtmlTag(data);
                data = FilterUnSafeSQL(data);
                data = FilterSpecial(data);
            }
            return data;
        }
        #endregion
    }
}
